﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase;
using INTEX.DataBase.Tools;
using System.Linq;
namespace WPFExcelReport
{
    //
    public class YCAEKAPPRData : BaseFunction
    {

        public INTEXDBC INTEXDBC;
        SETINTEXDB INTEXDB = new SETINTEXDB();
        public YCAEKAPPRData()
            : base()
        {
            Helpers.Helper.GetDate(ref date, ref year, ref month, ref day);
        }
        public YCAEKAPPRData(OracleConnection _OracleDB, IntexReptServBLClient _proxy, string userID = "        ")
            : base(_OracleDB, _proxy, userID)
        {
            Helpers.Helper.GetDate(ref date, ref year, ref month, ref day);
            INTEXDBC = new INTEXDBC(_OracleDB, _proxy);
        }
        public YCAEKAPPRData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
            Helpers.Helper.GetDate(ref date, ref year, ref month, ref day);
        }
        public DataSet LoadSupplier()
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            DataRow dwData = INTEXDB.LIEFDAT.NewLIEFDATRow();
            dwData["LSMANDANT"] = "00";
            SResult = INTEXDBC.QueryLIEFDAT(dwData, 6);
            SReturn.Merge(SResult);
            return SReturn;
        }
        public DataSet GetOrderBySupplier(DataRow dwInput)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            DataRow dwData = INTEXDB.EKKOPF.NewRow();
            dwData["EKKMANDANT"] = "00";
            dwData["EKKLIEFNR"] =dwInput["LSLIEFNR"].ToString();
            SResult = INTEXDBC.QueryEKKOPF(dwData, 3);
            SReturn.Merge(SResult);
            return SReturn;
        }
        public DataSet BuildData(DataRow dwInput)
        {
            DataSet Result = new DataSet();
            DataSet Return = new DataSet();
            DataRow rowtemp = null;
            try
            {
                rowtemp = INTEXDB.EKKOPF.NewRow();
                rowtemp["EKKMANDANT"] = dwInput["EKPMANDANT"];
                rowtemp["EKKAUFKRS"] = dwInput["EKPAUFKRS"];
                rowtemp["EKKAUFLFD"] = dwInput["EKPAUFLFD"];
                Result = INTEXDBC.QueryEKKOPF(rowtemp);
                Return.Merge(Result);               
                #region=======Order======
                string EKPMANDANT = "";
                string EKPAUFKRS = "",EKPAUFLFD = "",EKPBEKZ = "",EKPKNTRNRL = "";
                EKPBEKZ = dwInput["EKPBEKZ"].ToString();
                EKPKNTRNRL = dwInput["EKPKNTRNRL"].ToString();
                OracleCommand or1 = OracleDB.CreateCommand();
                foreach (DataRow row in Return.Tables["EKKOPF"].Rows)
                {
                    rowtemp = INTEXDB.EKKOPF.NewRow();
                    rowtemp["EKKMANDANT"] = row["EKKMANDANT"];
                    rowtemp["EKKAUFKRS"] = row["EKKAUFKRS"];
                    rowtemp["EKKAUFLFD"] = row["EKKAUFLFD"];
                    Result = INTEXDBC.QueryLIEFDAT(rowtemp, 5);
                    Return.Merge(Result); 
                    Result = INTEXDBC.QueryLIEFKO(rowtemp, 5);
                    Return.Merge(Result);
                    Result = INTEXDBC.QueryWAPROF(rowtemp, 5);
                    Return.Merge(Result);
                    EKPMANDANT = row["EKKMANDANT"].ToString();
                    EKPAUFKRS = row["EKKAUFKRS"].ToString();
                    EKPAUFLFD = row["EKKAUFLFD"].ToString();                    
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR, "
                        + " EKPWAKZ,EKPBEKZ,EKPLIEFNR,EKPWE1,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ "
                        + " ,EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2, "
                        + " EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3, "
                        + " EKPZUABWE3,EKPZUABME3  ,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4, "
                        + " EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5, "
                        + " EKPZUABME5  ,EKPABRKZ5,  EKPZUABWERT,EKPKNTRNRL "
                        + " ,  utl_raw.cast_to_varchar2(EKPART) AS EKPART "
                        + " ,  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR "
                        + " , utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE EKPSA<>'AJ' AND EKPMANDANT='" + EKPMANDANT + "' "
                        + "  AND EKPAUFKRS='" + EKPAUFKRS + "' AND EKPAUFLFD='" + EKPAUFLFD + "'  "
                        + "  AND EKPBEKZ IN ( " + EKPBEKZ + ")  "
                        + "  AND ( trim(EKPKNTRNRL) IS " + EKPKNTRNRL + " NULL "
                        + "  or EXISTS (SELECT '1' FROM EKVRECH A "
                        + "  WHERE A.EKVMANDANT = P.EKPMANDANT  "
                        + "  AND TRIM(A.EKVFILLER)  = P.EKPAUFKRS||'-'||P.EKPAUFLFD||'-'||P.EKPAUFPOS "
                        + "  AND A.EKVKIR='K'  AND '" + EKPKNTRNRL + "' = 'NOT')) " 
                                ;
                    Result = Connect2Proxy(or1);

                    if (Result.Tables.Count > 0)
                    {
                        Result.Tables[0].TableName = "ICC_EKPOS";

                    }
                    Return.Merge(Result);
                    //rowtemp = INTEXDB.EKPOS.NewRow();
                    //rowtemp["EKPMANDANT"] = row["EKKMANDANT"];
                    //rowtemp["EKPAUFKRS"] = row["EKKAUFKRS"];
                    //rowtemp["EKPAUFLFD"] = row["EKKAUFLFD"];
                    //rowtemp["EKPBEKZ"] = dwInput["EKPBEKZ"];
                    //rowtemp["EKPKNTRNRL"] = dwInput["EKPKNTRNRL"];                    
                    //Result = INTEXDBC.QueryICC_EKPOS(rowtemp, 3);
                    //Return.Merge(Result);
                    rowtemp = INTEXDB.ICC_ABAUFM.NewRow();
                    rowtemp["ABAMANDANT"] = row["EKKMANDANT"];
                    rowtemp["ABAAUFKRS"] = row["EKKAUFKRS"];
                    rowtemp["ABAAUFLFD"] = row["EKKAUFLFD"];
                    Result = INTEXDBC.QueryICC_ABAUFM(rowtemp, 4);
                    Return.Merge(Result);
                    rowtemp = INTEXDB.ICC_ABAUFM.NewRow();
                    rowtemp["ABAMANDANT"] = row["EKKMANDANT"];
                    rowtemp["ABAAUFKRS"] = row["EKKAUFKRS"];
                    rowtemp["ABAAUFLFD"] = row["EKKAUFLFD"];
                    Result = INTEXDBC.QueryICC_ABAUFM(rowtemp, 1);
                    if (Result.Tables.Count > 0)
                    {
                        Result.Tables[0].TableName = "ABPOS";
                        Return.Merge(Result);
                    }
                    rowtemp = INTEXDB.ICC_ABAUFM.NewRow();
                    rowtemp["ABAMANDANT"] = row["EKKMANDANT"];
                    rowtemp["ABAAUFKRS"] = row["EKKAUFKRS"];
                    rowtemp["ABAAUFLFD"] = row["EKKAUFLFD"];
                    Result = INTEXDBC.QueryICC_ABAUFM(rowtemp, 21);
                    if (Result.Tables.Count > 0)
                    {
                        Result.Tables[0].TableName = "MAINFABRIC";
                        Return.Merge(Result);
                    }
                    rowtemp = INTEXDB.EKVRECH.NewRow();
                    rowtemp["EKVMANDANT"] = row["EKKMANDANT"];
                    rowtemp["EKVRECHNR"] = row["EKKAUFKRS"].ToString() + "-" + row["EKKAUFLFD"].ToString();
                    Result = INTEXDBC.QueryEKVRECH(rowtemp, 2);
                    Return.Merge(Result);
                }
                #endregion
                #region========material==========
                /*var query = Return.Tables["ICC_EKPOS"].AsEnumerable()
                        .Select(row => new
                        {
                            ARTG = row.Field<string>("EKPARTGR"),
                            ART = row.Field<string>("EKPART"),
                            VAR = row.Field<string>("EKPVAR"),
                            COLOR = row.Field<string>("EKPFARBE"),
                            SIZE = row.Field<string>("EKPGROE")
                        }).Distinct();*/
                var query = Return.Tables["ICC_EKPOS"].AsEnumerable()
        .Select(row => new
        {
            ARTG = row.Field<string>("EKPARTGR"),
            ART = row.Field<string>("EKPART"),
            VAR = row.Field<string>("EKPVAR") 
        }).Distinct();
                foreach (var row in query)
                {
                    switch (row.ARTG)
                    {
                        case "1":
                            break;
                        case "2":
                            break;
                        case "3":
                            rowtemp = INTEXDB.WASTAMM.NewRow();
                            rowtemp["WAMANDANT"] = "00";
                            rowtemp["WAART"] = row.ART;
                            rowtemp["WAVAR"] =Left(row.VAR,2);
                            Result = INTEXDBC.QueryWASTAMM(rowtemp, 2);
                            Return.Merge(Result);
                            rowtemp = INTEXDB.STUELI.NewRow();
                            rowtemp["STUELIMANDANT"] = "00";
                            rowtemp["STUELISAPD"] = row.ART;
                            Result = INTEXDBC.QuerySTUELI(rowtemp, 3);
                            Return.Merge(Result);
                            //FSSTAMM;FSFARBEN;FSSTUELI;FSGROE
                            rowtemp = INTEXDB.WAFARBEN.NewRow();
                            rowtemp["WAFMANDANT"] = "00";
                            rowtemp["WAFART"] = row.ART;
                            rowtemp["WAFVAR"] = Left(row.VAR, 2);
                            Result = INTEXDBC.QueryWAFARBEN(rowtemp, 2);
                            Return.Merge(Result);
                            break;
                        case "4":
                            break;
                        case "5":
                            rowtemp = INTEXDB.FSSTAMM.NewRow();
                            rowtemp["FSMANDANT"] = "00";
                            rowtemp["FSART"] = row.ART;
                            rowtemp["FSVAR"] = Left(row.VAR, 2);
                            Result = INTEXDBC.QueryFSSTAMM(rowtemp, 1);
                            Return.Merge(Result);
                            //FSSTAMM;FSFARBEN;FSSTUELI;FSGROE
                            rowtemp = INTEXDB.FSFARBEN.NewRow();
                            rowtemp["FSFMANDANT"] = "00";
                            rowtemp["FSFART"] = row.ART;
                            rowtemp["FSFVAR"] = Left(row.VAR, 2);
                            Result = INTEXDBC.QueryFSFARBEN(rowtemp, 2);
                            Return.Merge(Result);
                            rowtemp = INTEXDB.FSGROE.NewRow();
                            rowtemp["FSGMANDANT"] = "00";
                            rowtemp["FSGART"] = row.ART;
                            rowtemp["FSGVAR"] = row.VAR;
                            Result = INTEXDBC.QueryFSGROE(rowtemp, 2);
                            Return.Merge(Result);
                            break;
                        case "6":
                            break;
                        case "7":
                            break;
                        case "8":
                            rowtemp = INTEXDB.MSSTAMM.NewRow();
                            rowtemp["MSMANDANT"] = "00";
                            rowtemp["MSMAT"] = Left(row.ART, 14);
                            rowtemp["MSVAR"] = row.VAR;
                            Result = INTEXDBC.QueryMSSTAMM(rowtemp, 1);
                            Return.Merge(Result);
                            break;
                        default:
                            break;
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
       

    }
}